﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.ServiceModel;
using System.IO;
using System.Web.Script.Serialization;
using System.ServiceModel.Web;
using System.Text;
using System.Configuration;
using ATPLDAL;
using HomeCareLibrary.Core.DataContracts;
using HomeCareLibrary.Core.JsonObjects;

namespace HomeWizardServices 
{
    public class DataAccess
    {

        public static Stream getApp_PES_GetFeaturesByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetFeaturesByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row.Table.Columns.Contains("user_id") && row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row.Table.Columns.Contains("home_number") && row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row.Table.Columns.Contains("featurestring") && row["featurestring"].ToString() != "")
                    {
                        dictionary.Add("featurestring" + count, row["featurestring"].ToString().Replace("'",""));
                    }
                    else
                    {
                        dictionary.Add("featurestring" + count, "No Information");
                    }
                    if (row.Table.Columns.Contains("dwelling_type") && row["dwelling_type"].ToString() != "")
                    {
                        dictionary.Add("dwelling_type" + count, row["dwelling_type"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_type" + count, "No Information");
                    }                    
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
             
        }

        public static Stream getFeedBack(string feedback)
        {
            
            string str4;
            string str2 = "";

            {
                SqlParameter[] commandParameters = new SqlParameter[3];
                commandParameters[0] = new SqlParameter("@op", SqlDbType.Int);
                commandParameters[0].Value = 13;
                commandParameters[1] = new SqlParameter("@Result", SqlDbType.Int);
                commandParameters[1].Direction = ParameterDirection.Output;
                commandParameters[2] = new SqlParameter("@FeedBack", SqlDbType.NVarChar);
                commandParameters[2].Value = feedback;
                SqlHelper.ExecuteNonQuery(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_User", commandParameters);
                if (Convert.ToInt32(commandParameters[1].Value) == 1)
                {
                    str2 = "success"; 
                }
                str2 = "Fail";
            }

            string str3 = Convert.ToString(OperationContext.Current.RequestContext.RequestMessage.Headers.To);
            string WebServiceName = "getFeedBack";
            string DataForService = str3;
            using (StreamReader reader = new StreamReader(WriteJson(new { status = str2 }), Encoding.Default))
            {
                str4 = reader.ReadToEnd();
            }
            string DataRecived = str4;

            {
                SqlParameter[] commandParameters = new SqlParameter[5];
                commandParameters[0] = new SqlParameter("@op", SqlDbType.Int);
                commandParameters[0].Value = 1;
                commandParameters[1] = new SqlParameter("@WebServiceName", SqlDbType.VarChar);
                commandParameters[1].Value = WebServiceName;
                commandParameters[2] = new SqlParameter("@DataRequested", SqlDbType.VarChar);
                commandParameters[2].Value = DataForService;
                commandParameters[3] = new SqlParameter("@Result", SqlDbType.Int);
                commandParameters[3].Direction = ParameterDirection.Output;
                commandParameters[4] = new SqlParameter("@DataRecived", SqlDbType.VarChar);
                commandParameters[4].Value = DataRecived;
                SqlHelper.ExecuteNonQuery(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_WebserviceList", commandParameters);
            }
            return WriteJson(new { status = str2 });

        }

        public static Stream getIdeas(Stream stream)
        {
            string str11;
            int num6;
            int num7;
            string str13;
            string[] strArray = new string[] { 
                "idea_code", "display_icon_code", "idea_title", "idea_content", "idea_timing", "idea_score", "idea_priority", "idea_type", "idea_benefit_content", "idea_energy_savings_score", "idea_energy_savings_content", "idea_cost_score", "idea_more_info_link", "idea_knowledge_level", "TGMS", "benefits_str", 
                "idea_excluded_features"
             };
            
            StreamReader reader = new StreamReader(stream);
            string[] strArray2 =reader.ReadToEnd().Split(new char[] { '/' });
            int num2 = 7;
            string str10 = "no";
            
            string[] strarray_dwell_priority_kl_loc = strArray2[0].Split(new char[] { '=' });
            string str = strarray_dwell_priority_kl_loc[1];

            string[] strarray_feature_string = strArray2[1].Split(new char[] { '=' });
            string str2 = strarray_feature_string[1];

            string[] strarray_ps_goal_string = strArray2[2].Split(new char[] { '=' });
            string str3 = strarray_ps_goal_string[1];

            string[] strarray_turn_offs = strArray2[3].Split(new char[] { '=' });
            string str4 = strarray_turn_offs[1];

            string[] strarray_startmonth = strArray2[4].Split(new char[] { '=' });
            string str5 = strarray_startmonth[1];

            string[] strarray_startyear = strArray2[5].Split(new char[] { '=' });
            string str6 = strarray_startyear[1];

            string[] strarray_requestedmonth = strArray2[6].Split(new char[] { '=' });
            string str7 = strarray_requestedmonth[1];

            string[] strarray_requestedyear = strArray2[7].Split(new char[] { '=' });
            string str8 = strarray_requestedyear[1];
            bool flag = false;
            int index = -1;

            DataSet set = new DataSet();

            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@dwell_priority_kl_loc", SqlDbType.NVarChar);
                commandParameters[0].Value = strarray_dwell_priority_kl_loc[1];
                commandParameters[1] = new SqlParameter("@feature_string", SqlDbType.NVarChar);
                commandParameters[1].Value = strarray_feature_string[1];
                commandParameters[2] = new SqlParameter("@ps_goal_string", SqlDbType.NVarChar);
                commandParameters[2].Value = strarray_ps_goal_string[1];
                commandParameters[3] = new SqlParameter("@turn_offs", SqlDbType.NVarChar);
                commandParameters[3].Value = strarray_turn_offs[1];
                commandParameters[4] = new SqlParameter("@startmonth", SqlDbType.Int);
                commandParameters[4].Value = strarray_startmonth[1];
                commandParameters[5] = new SqlParameter("@startyear", SqlDbType.Int);
                commandParameters[5].Value = strarray_startyear[1];
                commandParameters[6] = new SqlParameter("@requestedmonth", SqlDbType.Int);
                commandParameters[6].Value = strarray_requestedmonth[1];
                commandParameters[7] = new SqlParameter("@requestedyear", SqlDbType.Int);
                commandParameters[7].Value = strarray_requestedyear[1];
                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_Ideas", commandParameters);
            }

            Dictionary<string, string>[] dictionaryArray = new Dictionary<string, string>[num2];
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count < num2))
            {
                str10 = "yes";
                for (num6 = 0; num6 < set.Tables[0].Rows.Count; num6++)
                {
                    flag = false;
                    str11 = set.Tables[0].Rows[num6]["idea_excluded_features"].ToString().Trim();
                    if (str11.Length > 0)
                    {
                        flag = isInclude(strarray_feature_string, str11);
                    }
                    if (!flag)
                    {
                        index++;
                        dictionaryArray[index] = new Dictionary<string, string>();
                        num7 = 0;
                        while (num7 < strArray.Length)
                        {
                            dictionaryArray[index].Add(strArray[num7], set.Tables[0].Rows[num6][strArray[num7]].ToString());
                            num7++;
                        }
                    }
                }
            }
            else
            {
                index = 0;
            }
            if ((set.Tables[0].Rows.Count > 0) && (index < num2))
            {
                for (num6 = 0; num6 < set.Tables[0].Rows.Count; num6++)
                {
                    while ((index < num2) && (num6 < set.Tables[0].Rows.Count))
                    {
                        flag = false;
                        str11 = set.Tables[0].Rows[num6]["idea_excluded_features"].ToString().Trim();
                        if (str11.Length > 0)
                        {
                            flag = isInclude(strarray_feature_string, str11);
                        }
                        if (!flag)
                        {
                            dictionaryArray[index] = new Dictionary<string, string>();
                            num7 = 0;
                            while (num7 < strArray.Length)
                            {
                                dictionaryArray[index].Add(strArray[num7], set.Tables[0].Rows[num6][strArray[num7]].ToString());
                                num7++;
                            }
                            index++;
                            num6++;
                        }
                    }
                }
            }
            else
            {
                dictionaryArray = new Dictionary<string, string>[] { new Dictionary<string, string>() };
                for (num7 = 0; num7 < strArray.Length; num7++)
                {
                    dictionaryArray[0].Add(strArray[num7], "No Information");
                }
            }
            
            string str12 = Convert.ToString(OperationContext.Current.RequestContext.RequestMessage.Headers.To);
            string WebServiceName = "getIdeas";
            string DataForService = "";
            if (str4 == "(null)")
            {
                str4 = "no";
                DataForService = str12 + "/DwellingInfo=" + str + "/Featurestring=" + str2 + "/GoalString=" + str3 + "/TurnOffsString=" + str4 + "/StartMonth=" + str5 + "/StartYear=" + str6 + "/RequestedMonth=" + str7 + "/RequestedYear=" + str8;
            }
            else
            {
                DataForService = str12 + "/DwellingInfo=" + str + "/Featurestring=" + str2 + "/GoalString=" + str3 + "/TurnOffsString=" + str4 + "/StartMonth=" + str5 + "/StartYear=" + str6 + "/RequestedMonth=" + str7 + "/RequestedYear=" + str8;
            }
            using (StreamReader reader2 = new StreamReader(WriteJson(new { dictionary = dictionaryArray }), Encoding.Default))
            {
                str13 = reader2.ReadToEnd();
            }
            string DataRecived = str13;

            {
                SqlParameter[] commandParameters = new SqlParameter[5];
                commandParameters[0] = new SqlParameter("@op", SqlDbType.Int);
                commandParameters[0].Value = 1;
                commandParameters[1] = new SqlParameter("@WebServiceName", SqlDbType.VarChar);
                commandParameters[1].Value = WebServiceName;
                commandParameters[2] = new SqlParameter("@DataRequested", SqlDbType.VarChar);
                commandParameters[2].Value = DataForService;
                commandParameters[3] = new SqlParameter("@Result", SqlDbType.Int);
                commandParameters[3].Direction = ParameterDirection.Output;
                commandParameters[4] = new SqlParameter("@DataRecived", SqlDbType.VarChar);
                commandParameters[4].Value = DataRecived;
                SqlHelper.ExecuteNonQuery(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_WebserviceList", commandParameters);
            }
            return WriteJson(new { countdata = str10, dictionary = dictionaryArray });
        }

        public static Stream getTips(Stream stream)
        {
            StreamReader reader = new StreamReader(stream);
            string[] postedParams = reader.ReadToEnd().Split(new char[] { '/' });
            string[] dwellPriorityKLLocParam = postedParams[0].Split(new char[] { '=' });
            string dwellPriorityKLLoc = dwellPriorityKLLocParam[1];

            string[] featureStringParam = postedParams[1].Split(new char[] { '=' });
            string featureString = featureStringParam[1];

            string[] psGoalStringParam = postedParams[2].Split(new char[] { '=' });
            string psGoalString = psGoalStringParam[1];

            string[] turnOffsParam = postedParams[3].Split(new char[] { '=' });
            string turnOffs = turnOffsParam[1];

            string[] startMonthParam = postedParams[4].Split(new char[] { '=' });
            string startMonth = startMonthParam[1];

            string[] startYearParam = postedParams[5].Split(new char[] { '=' });
            string startYear = startYearParam[1];

            string[] requestedMonthParam = postedParams[6].Split(new char[] { '=' });
            string requestedMonth = requestedMonthParam[1];

            string[] requestedYearParam = postedParams[7].Split(new char[] { '=' });
            string requestedYear = requestedYearParam[1];

            return getTips(dwellPriorityKLLoc, featureString, psGoalString, turnOffs, startMonth, startYear, requestedMonth, requestedYear);
        }

        public static Stream getTips(string _dwellPriorityKLLoc, string _featureString, string _psGoalString, string _turnOffs, string _startMonth, string _startYear, string _requestedMonth, string _requestedYear)
        {
            string str11;
            int num5;
            int num6;
            string str13;
            string[] strArray = new string[] { 
                "tip_code", "display_icon_code", "tip_title", "tip_content", "tip_timing", "tip_priority", "tip_type", "tip_benefit_content", "tip_energy_savings_score", "tip_energy_savings_content", "tip_cost_score", "tip_cost_content", "tip_more_info_link", "tip_knowledge_level", "tip_score", "TGMS", 
                "benefits_str"
             };
            int num2 = 7;
            string str10 = "no";

            #region old commented code
            //StreamReader reader = new StreamReader(stream);
            //string[] strArray2 = reader.ReadToEnd().Split(new char[] { '/' });
            

            //string[] strarray_dwell_priority_kl_loc = strArray2[0].Split(new char[] { '=' });
            //string str = strarray_dwell_priority_kl_loc[1];

            //string[] strarray_feature_string = strArray2[1].Split(new char[] { '=' });
            //string str2 = strarray_feature_string[1];

            //string[] strarray_ps_goal_string = strArray2[2].Split(new char[] { '=' });
            //string str3 = strarray_ps_goal_string[1];

            //string[] strarray_turn_offs = strArray2[3].Split(new char[] { '=' });
            //string str4 = strarray_turn_offs[1];

            //string[] strarray_startmonth = strArray2[4].Split(new char[] { '=' });
            //string str5 = strarray_startmonth[1];

            //string[] strarray_startyear = strArray2[5].Split(new char[] { '=' });
            //string str6 = strarray_startyear[1];

            //string[] strarray_requestedmonth = strArray2[6].Split(new char[] { '=' });
            //string str7 = strarray_requestedmonth[1];

            //string[] strarray_requestedyear = strArray2[7].Split(new char[] { '=' });
            //string str8 = strarray_requestedyear[1];
            #endregion

            DataSet set = new DataSet();
            bool flag = false;
            int index = -1;

            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@dwell_priority_kl_loc", SqlDbType.NVarChar);
                commandParameters[0].Value = _dwellPriorityKLLoc; 
                commandParameters[1] = new SqlParameter("@feature_string", SqlDbType.NVarChar);
                commandParameters[1].Value = _featureString;
                commandParameters[2] = new SqlParameter("@ps_goal_string", SqlDbType.NVarChar);
                commandParameters[2].Value = _psGoalString;
                commandParameters[3] = new SqlParameter("@turn_offs", SqlDbType.NVarChar);
                commandParameters[3].Value = _turnOffs;
                commandParameters[4] = new SqlParameter("@startmonth", SqlDbType.Int);
                commandParameters[4].Value = _startMonth;
                commandParameters[5] = new SqlParameter("@startyear", SqlDbType.Int);
                commandParameters[5].Value = _startYear;
                commandParameters[6] = new SqlParameter("@requestedmonth", SqlDbType.Int);
                commandParameters[6].Value = _requestedMonth;
                commandParameters[7] = new SqlParameter("@requestedyear", SqlDbType.Int);
                commandParameters[7].Value = _requestedYear;
                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_Tips", commandParameters);
            }

            Dictionary<string, string>[] dictionaryArray = new Dictionary<string, string>[num2];
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count < num2))
            {
                str10 = "yes";
                for (num5 = 0; num5 < set.Tables[0].Rows.Count; num5++)
                {
                    flag = false;
                    str11 = set.Tables[0].Rows[num5]["idea_excluded_features"].ToString().Trim();
                    if (str11.Length > 0)
                    {
                        flag = _featureString.Contains(str11);
                    }
                    if (!flag)
                    {
                        index++;
                        dictionaryArray[index] = new Dictionary<string, string>();
                        num6 = 0;
                        while (num6 < strArray.Length)
                        {
                            dictionaryArray[num5].Add(strArray[num6], set.Tables[0].Rows[num5][strArray[num6]].ToString());
                            num6++;
                        }
                    }
                }
                
            }
            else
            {
                index = 0;
            }
            if ((set.Tables[0].Rows.Count > 0) && (index < num2))
            {
                num5 = 0;
                while ((index < num2) && (num5 < set.Tables[0].Rows.Count))
                {
                    flag = false;
                    str11 = set.Tables[0].Rows[num5]["tip_excluded_features"].ToString().Trim();
                    if (str11.Length > 0)
                    {
                        flag = _featureString.Contains(str11);
                    }
                    if (!flag)
                    {
                        dictionaryArray[index] = new Dictionary<string, string>();
                        num6 = 0;
                        while (num6 < strArray.Length)
                        {
                            dictionaryArray[index].Add(strArray[num6], set.Tables[0].Rows[num5][strArray[num6]].ToString().Trim());
                            num6++;
                        }
                        index++;
                        num5++;
                    }
                }
            }
            else
            {
                dictionaryArray = new Dictionary<string, string>[] { new Dictionary<string, string>() };
                for (num6 = 0; num6 < strArray.Length; num6++)
                {
                    dictionaryArray[0].Add(strArray[num6], "No Information");
                }
            }
            
            string str12 = Convert.ToString(OperationContext.Current.RequestContext.RequestMessage.Headers.To);
            string WebServiceName = "monthlytips";
            string DataForService = "";
            if (_turnOffs == "(null)")
            {
                _turnOffs = "no";
            }
            DataForService = str12 + "/DwellingInfo=" + _dwellPriorityKLLoc + "/Featurestring=" + _featureString + "/GoalString=" + _psGoalString + "/TurnOffsString=" + _turnOffs + "/StartMonth=" + _startMonth + "/StartYear=" + _startYear + "/RequestedMonth=" + _requestedMonth + "/RequestedYear=" + _requestedYear;

            using (StreamReader reader2 = new StreamReader(WriteJson(new { dictionary = dictionaryArray }), Encoding.Default))
            {
                str13 = reader2.ReadToEnd();
            }
            string DataRecived = str13;

            {
                SqlParameter[] commandParameters = new SqlParameter[5];
                commandParameters[0] = new SqlParameter("@op", SqlDbType.Int);
                commandParameters[0].Value = 1;
                commandParameters[1] = new SqlParameter("@WebServiceName", SqlDbType.VarChar);
                commandParameters[1].Value = WebServiceName;
                commandParameters[2] = new SqlParameter("@DataRequested", SqlDbType.VarChar);
                commandParameters[2].Value = DataForService;
                commandParameters[3] = new SqlParameter("@Result", SqlDbType.Int);
                commandParameters[3].Direction = ParameterDirection.Output;
                commandParameters[4] = new SqlParameter("@DataRecived", SqlDbType.VarChar);
                commandParameters[4].Value = DataRecived;
                SqlHelper.ExecuteNonQuery(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_WebserviceList", commandParameters);
            }
            return WriteJson(new { countdata = str10, dictionary = dictionaryArray });
        }

        public static Stream getRoutineTask(Stream stream)
        {







            Dictionary<string, string>[] dictionaryArray;
            int num5;
            string str14;
            string[] strArray = new string[] { 
                "task_code", "display_icon_code", "rc_task_title", "rc_task_content", "rc_rec_timing", "rc_priority", "rc_type", "rc_benefit_content", "rc_energy_savings_score", "rc_energy_savings_content", "rc_cost_score", "rc_cost_content", "rc_more_info_link", "rc_knowledge_level", "rc_cycle_timing", "rc_timing_months", 
                "TGMS", "benefits_str"
             };
            StreamReader reader = new StreamReader(stream);
            string[] strArray2 = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strarray_dwell_priority_kl_loc = strArray2[0].Split(new char[] { '=' });
            string str = strarray_dwell_priority_kl_loc[1];

            string[] strarray_feature_string = strArray2[1].Split(new char[] { '=' });
            string str2 = strarray_feature_string[1];

            string[] strarray_ps_goal_string = strArray2[2].Split(new char[] { '=' });
            string str3 = strarray_ps_goal_string[1];

            string[] strarray_turn_offs = strArray2[3].Split(new char[] { '=' });
            string str4 = strarray_turn_offs[1];

            string[] strarray_startmonth = strArray2[4].Split(new char[] { '=' });
            string str5 = strarray_startmonth[1];

            string[] strarray_startyear = strArray2[5].Split(new char[] { '=' });
            string str6 = strarray_startyear[1];

            string[] strarray_requestedmonth = strArray2[6].Split(new char[] { '=' });
            string str7 = strarray_requestedmonth[1];

            string[] strarray_requestedyear = strArray2[7].Split(new char[] { '=' });
            string str8 = strarray_requestedyear[1];

            string[] strarray_user_id = strArray2[8].Split(new char[] { '=' });
            string str9 = strarray_user_id[1];

            string[] strarray_home_number = strArray2[9].Split(new char[] { '=' });
            string str11 = strarray_home_number[1];
            string str10 = "no";
            DataSet set = new DataSet();
            bool flag = false;
            int index = 0;
            string str12 = "";

            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@dwell_priority_kl_loc", SqlDbType.NVarChar);
                commandParameters[0].Value = strarray_dwell_priority_kl_loc[1];
                commandParameters[1] = new SqlParameter("@feature_string", SqlDbType.NVarChar);
                commandParameters[1].Value = strarray_feature_string[1];
                commandParameters[2] = new SqlParameter("@ps_goal_string", SqlDbType.NVarChar);
                commandParameters[2].Value = strarray_ps_goal_string[1];
                commandParameters[3] = new SqlParameter("@turn_offs", SqlDbType.NVarChar);
                commandParameters[3].Value = strarray_turn_offs[1];
                commandParameters[4] = new SqlParameter("@startmonth", SqlDbType.Int);
                commandParameters[4].Value = strarray_startmonth[1];
                commandParameters[5] = new SqlParameter("@startyear", SqlDbType.Int);
                commandParameters[5].Value = strarray_startyear[1];
                commandParameters[6] = new SqlParameter("@requestedmonth", SqlDbType.Int);
                commandParameters[6].Value = strarray_requestedmonth[1];
                commandParameters[7] = new SqlParameter("@requestedyear", SqlDbType.Int);
                commandParameters[7].Value = strarray_requestedyear[1];
                commandParameters[8] = new SqlParameter("@user_ID", SqlDbType.Int);
                commandParameters[8].Value = strarray_user_id[1];
                commandParameters[9] = new SqlParameter("@home_number", SqlDbType.Int);
                commandParameters[9].Value = strarray_home_number[1];
                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_Routinetask", commandParameters);
            }
            
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                dictionaryArray = new Dictionary<string, string>[set.Tables[0].Rows.Count];
                for (int i = 0; i < set.Tables[0].Rows.Count; i++)
                {
                    int num4 = 0;
                    flag = false;
                    str12 = set.Tables[0].Rows[i]["rc_excluded_features"].ToString().Trim();
                    if (str12.Length > 0)
                    {
                        string[] strArray4 = str12.Split(new char[] { ',' });
                        while ((num4 < strArray4.Length) && !flag)
                        {
                            if (str2.IndexOf(strArray4[num4].Trim()) > 0)
                            {
                                flag = true;
                            }
                            else
                            {
                                num4++;
                            }
                        }
                    }
                    if (!flag)
                    {
                        dictionaryArray[index] = new Dictionary<string, string>();
                        num5 = 0;
                        while (num5 < strArray.Length)
                        {
                            dictionaryArray[index].Add(strArray[num5], set.Tables[0].Rows[i][strArray[num5]].ToString());
                            num5++;
                        }
                        index++;
                    }
                }
            }
            else
            {
                dictionaryArray = new Dictionary<string, string>[] { new Dictionary<string, string>() };
                for (num5 = 0; num5 < strArray.Length; num5++)
                {
                    dictionaryArray[0].Add(strArray[num5], "No Information");
                }
            }
            string str13 = Convert.ToString(OperationContext.Current.RequestContext.RequestMessage.Headers.To);
            string WebServiceName = "getRoutineTask";
            string DataForService = str13 + "/DwellingInfo=" + str + "/Featurestring=" + str2 + "/GoalString=" + str3 + "/TurnOffsString=" + str4 + "/StartMonth=" + str5 + "/StartYear=" + str6 + "/RequestedMonth=" + str7 + "/RequestedYear=" + str8+"/user_id="+str9+"/home_number="+str11;
            using (StreamReader reader2 = new StreamReader(WriteJson(new { dictionary = dictionaryArray }), Encoding.Default))
            {
                str14 = reader2.ReadToEnd();
            }
            string DataRecived = str14;

            {
                SqlParameter[] commandParameters = new SqlParameter[5];
                commandParameters[0] = new SqlParameter("@op", SqlDbType.Int);
                commandParameters[0].Value = 1;
                commandParameters[1] = new SqlParameter("@WebServiceName", SqlDbType.VarChar);
                commandParameters[1].Value = WebServiceName;
                commandParameters[2] = new SqlParameter("@DataRequested", SqlDbType.VarChar);
                commandParameters[2].Value = DataForService;
                commandParameters[3] = new SqlParameter("@Result", SqlDbType.Int);
                commandParameters[3].Direction = ParameterDirection.Output;
                commandParameters[4] = new SqlParameter("@DataRecived", SqlDbType.VarChar);
                commandParameters[4].Value = DataRecived;
                SqlHelper.ExecuteNonQuery(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_WebserviceList", commandParameters);
            }

            return WriteJson(new { countdata = str10, dictionary = dictionaryArray });
        }

        public static bool isInclude(string[] paramFeatureString, string paramExclusionFeatures)
        {
            string str = "";
            bool flag = false;
            bool flag2 = false;
            int index = 0;
            string[] strArray = str.Split(new char[] { ',' });
            while ((index < strArray.Length) && !flag2)
            {
                if (paramFeatureString[1].IndexOf(strArray[index].Trim()) > 0)
                {
                    flag = true;
                }
                else
                {
                    index++;
                }
            }
            return flag;
        }

        public static Stream getApp_Initial_Login(Stream stream)
        {
            DataTable set = new DataTable();
            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strArray_getUsername = str_getSplitPassParameters[0].Split(new char[] { '=' });
            string[] strArray_getPassword = str_getSplitPassParameters[1].Split(new char[] { '=' });
            string[] strArray_getpromo_code = str_getSplitPassParameters[2].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_week = str_getSplitPassParameters[3].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_day = str_getSplitPassParameters[4].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_hour = str_getSplitPassParameters[5].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_badge = str_getSplitPassParameters[6].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_popup = str_getSplitPassParameters[7].Split(new char[] { '=' });
            string[] strArray_getsentFrom = str_getSplitPassParameters[8].Split(new char[] { '=' });

            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();


            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[0].Value = strArray_getUsername[1];
                commandParameters[1] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                commandParameters[1].Value = strArray_getPassword[1];
                commandParameters[2] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(strArray_getpromo_code[1]))
                {
                    commandParameters[2].Value = strArray_getpromo_code[1];
                }
                else
                {
                    commandParameters[2].Value = DBNull.Value;
                }

                commandParameters[3] = new SqlParameter("@paramAppMoAlertWeek", SqlDbType.Int);
                commandParameters[3].Value = strArray_getapp_mo_alert_week[1];

                commandParameters[4] = new SqlParameter("@paramAppMoAlertDay", SqlDbType.Int);
                commandParameters[4].Value = strArray_getapp_mo_alert_day[1];


                commandParameters[5] = new SqlParameter("@paramAppMoAlertHour", SqlDbType.Int);
                commandParameters[5].Value = strArray_getapp_mo_alert_hour[1];

                commandParameters[6] = new SqlParameter("@paramAppMoAlertBadge", SqlDbType.Int);
                commandParameters[6].Value = strArray_getapp_mo_alert_badge[1];

                commandParameters[7] = new SqlParameter("@paramAppMoAlertPopup", SqlDbType.Int);
                commandParameters[7].Value = strArray_getapp_mo_alert_popup[1];

                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Initial_Login", commandParameters).Tables[0];
            }
            
            {
                foreach (DataRow row in set.Rows)
                {

                    if (row["initiallogin"].ToString() != "")
                    {
                        dictionary.Add("initiallogin", row["InitialLogin"].ToString());
                    }
                    else
                    {
                        dictionary.Add("initiallogin", "No Information");
                    }
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id", row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id", "No Information");
                    }
                    if (row["sponsor"].ToString() != "")
                    {
                        dictionary.Add("sponsor", row["sponsor"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsor", "No Information");
                    }
                    if (row["welcome_image"].ToString() != "")
                    {
                        dictionary.Add("welcome_image", row["welcome_image"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcome_image", "No Information");
                    }
                    if (row["welcome_image_chg_time"].ToString() != "")
                    {
                        dictionary.Add("welcome_image_chg_time", row["welcome_image_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcome_image_chg_time", "No Information");
                    }
                    if (row["iname_line"].ToString() != "")
                    {
                        dictionary.Add("iname_line", row["iname_line"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iname_line", "No Information");
                    }
                    if (row["icompany_name"].ToString() != "")
                    {
                        dictionary.Add("icompany_name", row["icompany_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("icompany_name", "No Information");
                    }
                    if (row["iemail_address"].ToString() != "")
                    {
                        dictionary.Add("iemail_address", row["iemail_address"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iemail_address", "No Information");
                    }
                    if (row["iphone_number"].ToString() != "")
                    {
                        dictionary.Add("iphone_number", row["iphone_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iphone_number", "No Information");
                    }
                    if (row["itagline"].ToString() != "")
                    {
                        dictionary.Add("itagline", row["itagline"].ToString());
                    }
                    else
                    {
                        dictionary.Add("itagline", "No Information");
                    }
                    if (row["ilogo_pic"].ToString() != "")
                    {
                        dictionary.Add("ilogo_pic", row["ilogo_pic"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ilogo_pic", "No Information");
                    }
                    if (row["iwebsite"].ToString() != "")
                    {
                        dictionary.Add("iwebsite", row["iwebsite"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iwebsite", "No Information");
                    }
                    if (row["spon_change_time"].ToString() != "")
                    {
                        dictionary.Add("spon_change_time", row["spon_change_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("spon_change_time", "No Information");
                    }
                }
            }

            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_Initial_Login(SponsorData sponsorData)
        {
            DataTable set = new DataTable();
            
            Dictionary<string, object> dictionary;
            dictionary = new Dictionary<string, object>();
            
            SqlParameter[] commandParameters = new SqlParameter[8];
            commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
            commandParameters[0].Value = sponsorData.userName;
            commandParameters[1] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
            commandParameters[1].Value = sponsorData.password;
            commandParameters[2] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
            if (!String.IsNullOrEmpty(sponsorData.promoCode))
            {
                commandParameters[2].Value = sponsorData.promoCode;
            }
            else
            {
                commandParameters[2].Value = DBNull.Value;
            }

            commandParameters[3] = new SqlParameter("@paramAppMoAlertWeek", SqlDbType.Int);
            commandParameters[3].Value = sponsorData.appMoAlertWeek;

            commandParameters[4] = new SqlParameter("@paramAppMoAlertDay", SqlDbType.Int);
            commandParameters[4].Value = sponsorData.appMoAlertDay;


            commandParameters[5] = new SqlParameter("@paramAppMoAlertHour", SqlDbType.Int);
            commandParameters[5].Value = sponsorData.appMoAlertHour;

            commandParameters[6] = new SqlParameter("@paramAppMoAlertBadge", SqlDbType.Int);
            commandParameters[6].Value = sponsorData.appMoAlertBadge;

            commandParameters[7] = new SqlParameter("@paramAppMoAlertPopup", SqlDbType.Int);
            commandParameters[7].Value = sponsorData.appMoAlertPopup;

            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Initial_Login", commandParameters).Tables[0];

            string initialLogin = string.Empty;
            string sponsorDataString = string.Empty;

            foreach (DataRow row in set.Rows)
            {
                if (row.Table.Columns.Contains("initialLogin") && row["initialLogin"].ToString() != "")
                {
                    initialLogin = row["initialLogin"].ToString();

                    dictionary.Add("initialLogin", initialLogin);

                    if (row.Table.Columns.Contains("user_id") && row["user_id"].ToString() != "")
                    {
                        dictionary.Add("userId", Convert.ToInt32(row["user_id"].ToString()));
                    }
                    else
                    {
                        dictionary.Add("userId", null);
                    }

                    if (row.Table.Columns.Contains("user_name") && row["user_name"].ToString() != "")
                    {
                        dictionary.Add("userName", row["user_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("userName", null);
                    }

                    if (row.Table.Columns.Contains("password") && row["password"].ToString() != "")
                    {
                        dictionary.Add("password", row["password"].ToString());
                    }
                    else
                    {
                        dictionary.Add("password", null);
                    }

                    if (row.Table.Columns.Contains("sponsor") && row["sponsor"] != DBNull.Value)
                    {
                        dictionary.Add("promoCode", row["sponsor"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promoCode", null);
                    }

                    if (row.Table.Columns.Contains("welcome_image") && row["welcome_image"] != DBNull.Value)
                    {
                        dictionary.Add("welcomeImage", row["welcome_image"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImage", null);
                    }

                    if (row.Table.Columns.Contains("welcome_image_chg_time") && row["welcome_image_chg_time"] != DBNull.Value)
                    {
                        dictionary.Add("welcomeImageChangeTime", row["welcome_image_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImageChangeTime", null);
                    }

                    if (row.Table.Columns.Contains("iname_line") && row["iname_line"] != DBNull.Value)
                    {
                        dictionary.Add("iNameLine", row["iname_line"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iNameLine", null);
                    }
                    if (row.Table.Columns.Contains("iadmin_link") && row["iadmin_link"] != DBNull.Value)
                    {
                        dictionary.Add("iAdminLink", row["iadmin_link"].ToString());
                    }
                    else
                    {
                        if (row.Table.Columns.Contains("icompany_name") && row["icompany_name"] != DBNull.Value)
                        {
                            dictionary.Add("iAdminLink", row["icompany_name"].ToString());
                        }
                        else
                        {
                            dictionary.Add("iAdminLink", "Compliments Of");
                        } 
                    }
                    if (row.Table.Columns.Contains("icompany_name") && row["icompany_name"] != DBNull.Value)
                    {
                        dictionary.Add("iCompanyName", row["icompany_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iCompanyName", null);
                    }
                    if (row.Table.Columns.Contains("iemail_address") && row["iemail_address"] != DBNull.Value)
                    {
                        dictionary.Add("iEmailAddress", row["iemail_address"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iEmailAddress", null);
                    }
                    if (row.Table.Columns.Contains("iphone_number") && row["iphone_number"] != DBNull.Value)
                    {
                        dictionary.Add("iPhoneNumber", row["iphone_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iPhoneNumber", null);
                    }
                    if (row.Table.Columns.Contains("itagline") && row["itagline"] != DBNull.Value)
                    {
                        dictionary.Add("iTagLine", row["itagline"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iTagLine", null);
                    }

                    if (row.Table.Columns.Contains("iwebsite") && row["iwebsite"] != DBNull.Value)
                    {
                        dictionary.Add("iWebSite", row["iwebsite"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iWebSite", null);
                    }

                    if (row.Table.Columns.Contains("spon_change_time") && row["spon_change_time"] != DBNull.Value)
                    {
                        dictionary.Add("sponChangeTime", row["spon_change_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponChangeTime", null);
                    }

                    if (row.Table.Columns.Contains("ilogo_pic") && row["ilogo_pic"] != DBNull.Value)
                    {
                        dictionary.Add("iLogoPic", row["ilogo_pic"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPic", null);
                    }

                    if (row.Table.Columns.Contains("ilogo_pic_chg_time") && row["ilogo_pic_chg_time"] != DBNull.Value)
                    {
                        dictionary.Add("iLogoPicChangeTime", row["ilogo_pic_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPicChangeTime", null);
                    }
                    if (row.Table.Columns.Contains("sponsorStatus") && row["sponsorStatus"] != DBNull.Value)
                    {
                        dictionary.Add("sponsorStatus", row["sponsorStatus"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsorStatus", null);
                    }
                }
                else
                {
                    dictionary.Add("initialLogin", null);
                }
            }
            

            return WriteJson(dictionary);
        }

        public static Stream getApp_Forgot_Password(Stream stream)
        {
            DataTable set = new DataTable();
            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strArray_getUsername = str_getSplitPassParameters[0].Split(new char[] { '=' });
            string[] strArray_getMode = str_getSplitPassParameters[1].Split(new char[] { '=' });
            string[] strArray_getsentFrom = str_getSplitPassParameters[2].Split(new char[] { '=' });

            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();

            {
                SqlParameter[] commandParameters = new SqlParameter[2];
                commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[0].Value = strArray_getUsername[1];
                commandParameters[1] = new SqlParameter("@paramMode", SqlDbType.NVarChar);
                commandParameters[1].Value = strArray_getMode[1];
                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_Forgot_Password", commandParameters).Tables[0];
            }

            if (strArray_getsentFrom[1] == "iPhoneApp" || strArray_getsentFrom[1] == "webUser")
            {
                foreach (DataRow row in set.Rows)
                {

                    if (row["user_name"].ToString() != "")
                    {
                        dictionary.Add("user_name", row["user_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_name", "No Information");
                    }
                    if (row["password"].ToString() != "")
                    {
                        dictionary.Add("password", row["password"].ToString());
                    }
                    else
                    {
                        dictionary.Add("password", "No Information");
                    }
                    if (row["forgotPassword"].ToString() != "")
                    {
                        dictionary.Add("forgotPassword", row["forgotPassword"].ToString());
                    }
                    else
                    {
                        dictionary.Add("forgotPassword", "No Information");
                    }
                }
            }
            else 
            {
                dictionary.Add("forgotPassword", "No Information");
            }
            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_Forgot_Password(ForgotPasswordRequest forgotPasswordRequest)
        {
            DataTable set = new DataTable();
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
            commandParameters[0].Value = forgotPasswordRequest.UserName;// strArray_getUsername[1];
            commandParameters[1] = new SqlParameter("@paramMode", SqlDbType.NVarChar);
            commandParameters[1].Value = forgotPasswordRequest.Mode;// strArray_getMode[1];
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_Forgot_Password", commandParameters).Tables[0];
            
            var dictionaryData = new Dictionary<string, string>();
            
            if (set.Rows.Count > 0)
            {
                DataRow row = set.Rows[0];
                dictionaryData.Add("forgotPassword", row["forgotPassword"].ToString());
            }

            
            return WriteJson(dictionaryData);
        }

        public static Stream getApp_Initial_Setup(Stream stream)
        {
            DataTable set = new DataTable();
            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });
            
            string[] strArray_getUsername = str_getSplitPassParameters[0].Split(new char[] { '=' });
            string[] strArray_getPassword = str_getSplitPassParameters[1].Split(new char[] { '=' });            
            string[]  strArray_getpromo_code = str_getSplitPassParameters[2].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_week = str_getSplitPassParameters[3].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_day = str_getSplitPassParameters[4].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_hour = str_getSplitPassParameters[5].Split(new char[] { '=' });
            string[] strArray_getapp_mo_alert_badge = str_getSplitPassParameters[6].Split(new char[] { '=' });            
            string[] strArray_getapp_mo_alert_popup = str_getSplitPassParameters[7].Split(new char[] { '=' });
            string[] strArray_getsentFrom = str_getSplitPassParameters[8].Split(new char[] { '=' });
            
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            
            DataSet ds = new DataSet();
            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[0].Value = strArray_getUsername[1];
                commandParameters[1] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                commandParameters[1].Value = strArray_getPassword[1];
                commandParameters[2] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(strArray_getpromo_code[1]))
                {
                    commandParameters[2].Value = strArray_getpromo_code[1];
                }
                else
                {
                    commandParameters[2].Value = DBNull.Value;
                }

                commandParameters[3] = new SqlParameter("@paramAppMoAlertWeek", SqlDbType.Int);
                commandParameters[3].Value = strArray_getapp_mo_alert_week[1];

                commandParameters[4] = new SqlParameter("@paramAppMoAlertDay", SqlDbType.Int);
                commandParameters[4].Value = strArray_getapp_mo_alert_day[1];


                commandParameters[5] = new SqlParameter("@paramAppMoAlertHour", SqlDbType.Int);
                commandParameters[5].Value = strArray_getapp_mo_alert_hour[1];

                commandParameters[6] = new SqlParameter("@paramAppMoAlertBadge", SqlDbType.Int);
                commandParameters[6].Value = strArray_getapp_mo_alert_badge[1];

                commandParameters[7] = new SqlParameter("@paramAppMoAlertPopup", SqlDbType.Int);
                commandParameters[7].Value = strArray_getapp_mo_alert_popup[1];

                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Initial_Setup", commandParameters).Tables[0];
            }

            {
                foreach (DataRow row in set.Rows)
                {

                    if (row["initialsetup"].ToString() != "")
                    {
                        dictionary.Add("initialsetup", row["InitialSetup"].ToString());
                    }
                    else
                    {
                        dictionary.Add("initialsetup", "No Information");
                    }
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id", row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id", "No Information");
                    }
                    if (row["sponsor"].ToString() != "")
                    {
                        dictionary.Add("sponsor", row["sponsor"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsor", "No Information");
                    }
                    if (row["welcome_image"].ToString() != "")
                    {
                        dictionary.Add("welcome_image", row["welcome_image"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcome_image", "No Information");
                    }
                    if (row["welcome_image_chg_time"].ToString() != "")
                    {
                        dictionary.Add("welcome_image_chg_time", row["welcome_image_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcome_image_chg_time", "No Information");
                    }
                    if (row["iname_line"].ToString() != "")
                    {
                        dictionary.Add("iname_line", row["iname_line"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iname_line", "No Information");
                    }
                    if (row["icompany_name"].ToString() != "")
                    {
                        dictionary.Add("icompany_name", row["icompany_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("icompany_name", "No Information");
                    }
                    if (row["iemail_address"].ToString() != "")
                    {
                        dictionary.Add("iemail_address", row["iemail_address"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iemail_address", "No Information");
                    }
                    if (row["iphone_number"].ToString() != "")
                    {
                        dictionary.Add("iphone_number", row["iphone_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iphone_number", "No Information");
                    }
                    if (row["itagline"].ToString() != "")
                    {
                        dictionary.Add("itagline", row["itagline"].ToString());
                    }
                    else
                    {
                        dictionary.Add("itagline", "No Information");
                    }
                    if (row["ilogo_pic"].ToString() != "")
                    {
                        dictionary.Add("ilogo_pic", row["ilogo_pic"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ilogo_pic", "No Information");
                    }
                    if (row["iwebsite"].ToString() != "")
                    {
                        dictionary.Add("iwebsite", row["iwebsite"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iwebsite", "No Information");
                    }
                    if (row["spon_change_time"].ToString() != "")
                    {
                        dictionary.Add("spon_change_time", row["spon_change_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("spon_change_time", "No Information");
                    }
                }
            }

            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_Initial_Setup(SponsorData sponsorData)
        {
            DataTable set = new DataTable();
            Dictionary<string, object> dictionary;
            dictionary = new Dictionary<string, object>();

            DataSet ds = new DataSet();
            {
                SqlParameter[] commandParameters = new SqlParameter[8];
                commandParameters[0] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[0].Value = sponsorData.userName;// strArray_getUsername[1];
                commandParameters[1] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                commandParameters[1].Value = sponsorData.password;// strArray_getPassword[1];
                commandParameters[2] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(sponsorData.promoCode))// strArray_getpromo_code[1]))
                {
                    commandParameters[2].Value = sponsorData.promoCode;// strArray_getpromo_code[1];
                }
                else
                {
                    commandParameters[2].Value = DBNull.Value;
                }

                commandParameters[3] = new SqlParameter("@paramAppMoAlertWeek", SqlDbType.Int);
                commandParameters[3].Value = sponsorData.appMoAlertWeek;// strArray_getapp_mo_alert_week[1];

                commandParameters[4] = new SqlParameter("@paramAppMoAlertDay", SqlDbType.Int);
                commandParameters[4].Value = sponsorData.appMoAlertDay;// strArray_getapp_mo_alert_day[1];


                commandParameters[5] = new SqlParameter("@paramAppMoAlertHour", SqlDbType.Int);
                commandParameters[5].Value = sponsorData.appMoAlertHour;// strArray_getapp_mo_alert_hour[1];

                commandParameters[6] = new SqlParameter("@paramAppMoAlertBadge", SqlDbType.Int);
                commandParameters[6].Value = sponsorData.appMoAlertBadge;// strArray_getapp_mo_alert_badge[1];

                commandParameters[7] = new SqlParameter("@paramAppMoAlertPopup", SqlDbType.Int);
                commandParameters[7].Value = sponsorData.appMoAlertPopup;// strArray_getapp_mo_alert_popup[1];

                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Initial_Setup", commandParameters).Tables[0];
            }

            string initialSetup = string.Empty;
            string sponsorDataString = string.Empty;

            foreach (DataRow row in set.Rows)
            {
                if (row.Table.Columns.Contains("initialsetup") && row["initialsetup"].ToString() != "")
                {
                    initialSetup = row["initialsetup"].ToString();

                    dictionary.Add("initialSetup", initialSetup);

                    if (row.Table.Columns.Contains("user_id") && row["user_id"].ToString() != "")
                    {
                        dictionary.Add("userId", Convert.ToInt32(row["user_id"].ToString()));
                    }
                    else
                    {
                        dictionary.Add("userId", null);
                    }

                    if (row.Table.Columns.Contains("user_name") && row["user_name"].ToString() != "")
                    {
                        dictionary.Add("userName", row["user_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("userName", null);
                    }

                    if (row.Table.Columns.Contains("password") && row["password"].ToString() != "")
                    {
                        dictionary.Add("password", row["password"].ToString());
                    }
                    else
                    {
                        dictionary.Add("password", null);
                    }

                    if (row.Table.Columns.Contains("sponsor") && row["sponsor"].ToString() != "")
                    {
                        dictionary.Add("promoCode", row["sponsor"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promoCode", null);
                    }

                    if (row.Table.Columns.Contains("welcome_image") && row["welcome_image"].ToString() != "")
                    {
                        dictionary.Add("welcomeImage", row["welcome_image"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImage", null);
                    }

                    if (row.Table.Columns.Contains("welcome_image_chg_time") && row["welcome_image_chg_time"].ToString() != "")
                    {
                        dictionary.Add("welcomeImageChangeTime", row["welcome_image_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImageChangeTime", null);
                    }


                    if (row.Table.Columns.Contains("iname_line") && row["iname_line"].ToString() != "")
                    {
                        dictionary.Add("iNameLine", row["iname_line"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iNameLine", null);
                    }
                    if (row.Table.Columns.Contains("icompany_name") && row["icompany_name"].ToString() != "")
                    {
                        dictionary.Add("iCompanyName", row["icompany_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iCompanyName", null);
                    }
                    if (row.Table.Columns.Contains("iemail_address") && row["iemail_address"].ToString() != "")
                    {
                        dictionary.Add("iEmailAddress", row["iemail_address"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iEmailAddress", null);
                    }
                    if (row.Table.Columns.Contains("iphone_number") && row["iphone_number"].ToString() != "")
                    {
                        dictionary.Add("iPhoneNumber", row["iphone_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iPhoneNumber", null);
                    }
                    if (row.Table.Columns.Contains("itagline") && row["itagline"].ToString() != "")
                    {
                        dictionary.Add("iTagLine", row["itagline"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iTagLine", null);
                    }

                    if (row.Table.Columns.Contains("iwebsite") && row["iwebsite"].ToString() != "")
                    {
                        dictionary.Add("iWebSite", row["iwebsite"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iWebSite", null);
                    }

                    if (row.Table.Columns.Contains("spon_change_time") && row["spon_change_time"].ToString() != "")
                    {
                        dictionary.Add("sponChangeTime", row["spon_change_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponChangeTime", null);
                    }

                    if (row.Table.Columns.Contains("ilogo_pic") && row["ilogo_pic"].ToString() != "")
                    {
                        dictionary.Add("iLogoPic", row["ilogo_pic"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPic", null);
                    }

                    if (row.Table.Columns.Contains("ilogo_pic_chg_time") && row["ilogo_pic_chg_time"].ToString() != "")
                    {
                        dictionary.Add("iLogoPicChangeTime", row["ilogo_pic_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPicChangeTime", null);
                    }
                    
                }
                else
                {
                    dictionary.Add("initialSetup", null);
                }
            }
            return WriteJson(dictionary);
        }

        public static Stream getApp_Reopen(Stream stream)
        {

            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strArray_getUserID = str_getSplitPassParameters[0].Split(new char[] { '=' });
            string[] strArray_getUserName = str_getSplitPassParameters[1].Split(new char[] { '=' });
            string[] strArray_getPassword = str_getSplitPassParameters[2].Split(new char[] { '=' });
            string[] strArray_getpromo_code = str_getSplitPassParameters[3].Split(new char[] { '=' });
            string[] strArray_getWelcomeImageChgTime = str_getSplitPassParameters[4].Split(new char[] { '=' });
            string[] strArray_getSponsorCchangeTime = str_getSplitPassParameters[5].Split(new char[] { '=' });
            string[] strArray_getIlogoPicChgTime = str_getSplitPassParameters[6].Split(new char[] { '=' });
            
            
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();

            DataTable set = new DataTable();

            {
                SqlParameter[] commandParameters = new SqlParameter[7];
                commandParameters[0] = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                commandParameters[0].Value = strArray_getUserID[1];
                commandParameters[1] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[1].Value = strArray_getUserName[1];
                commandParameters[2] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                commandParameters[2].Value = strArray_getPassword[1];
                commandParameters[3] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                commandParameters[3].Value = strArray_getpromo_code[1];
                commandParameters[4] = new SqlParameter("@paramWelcomeImageChgTime", SqlDbType.NVarChar);

                if (!String.IsNullOrEmpty(strArray_getWelcomeImageChgTime[1]))
                {
                    commandParameters[4].Value = strArray_getWelcomeImageChgTime[1];
                }
                else
                {
                    commandParameters[4].Value = DBNull.Value;
                }
                commandParameters[5] = new SqlParameter("@paramSponsorCchangeTime", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(strArray_getSponsorCchangeTime[1]))
                {
                    commandParameters[5].Value = strArray_getSponsorCchangeTime[1];
                }
                else
                {
                    commandParameters[5].Value = DBNull.Value;
                }
                commandParameters[6] = new SqlParameter("@paramIlogoPicChgTime", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(strArray_getIlogoPicChgTime[1]))
                {
                    commandParameters[6].Value = strArray_getIlogoPicChgTime[1];
                }
                else
                {
                    commandParameters[6].Value = DBNull.Value;
                }
                
                    set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Reopen", commandParameters).Tables[0];
               }

            User userObject = null;              
            if (set.Rows.Count > 0)
            {
                foreach (DataRow row in set.Rows)
                {
                    #region commented code
                    //if (row["user_id"].ToString() != "")
                    //{
                    //    dictionary.Add("user_id", row["user_id"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("user_id", "No Information");
                    //}
                    //if (row["user_name"].ToString() != "")
                    //{
                    //    dictionary.Add("user_name", row["user_name"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("user_name", "No Information");
                    //}
                    //if (row["password"].ToString() != "")
                    //{
                    //    dictionary.Add("password", row["password"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("password", "No Information");
                    //}
                    //if (row["sponsor"].ToString() != "")
                    //{
                    //    dictionary.Add("sponsor", row["sponsor"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("sponsor", "No Information");
                    //}
                    //if (row["welcome_image"].ToString() != "")
                    //{
                    //    dictionary.Add("welcome_image", row["welcome_image"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("welcome_image", "No Information");
                    //}
                    //if (row["welcome_image_chg_time"].ToString() != "")
                    //{
                    //    dictionary.Add("welcome_image_chg_time", row["welcome_image_chg_time"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("welcome_image_chg_time", "No Information");
                    //}
                    //if (row["iname_line"].ToString() != "")
                    //{
                    //    dictionary.Add("iname_line", row["iname_line"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("iname_line", "No Information");
                    //}

                    //if (row["icompany_name"].ToString() != "")
                    //{
                    //    dictionary.Add("icompany_name", row["icompany_name"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("icompany_name", "No Information");
                    //}

                    //if (row["iemail_address"].ToString() != "")
                    //{
                    //    dictionary.Add("iemail_address", row["iemail_address"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("iemail_address", "No Information");
                    //}
                    //if (row["iphone_number"].ToString() != "")
                    //{
                    //    dictionary.Add("iphone_number", row["iphone_number"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("iphone_number", "No Information");
                    //}

                    //if (row["itagline"].ToString() != "")
                    //{
                    //    dictionary.Add("itagline", row["itagline"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("itagline", "No Information");
                    //}
                    //if (row["ilogo_pic"].ToString() != "")
                    //{
                    //    dictionary.Add("ilogo_pic", row["ilogo_pic"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("ilogo_pic", "No Information");
                    //}
                    //if (row["iwebsite"].ToString() != "")
                    //{
                    //    dictionary.Add("iwebsite", row["iwebsite"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("iwebsite", "No Information");
                    //}
                    //if (row["spon_change_time"].ToString() != "")
                    //{
                    //    dictionary.Add("spon_change_time", row["spon_change_time"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("spon_change_time", "No Information");
                    //}

                    //if (row["SponsorDataAction"].ToString() != "")
                    //{
                    //    dictionary.Add("sponsordataaction", row["SponsorDataAction"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("sponsordataaction", "No Information");
                    //}

                    //if (row["PromocodeAction"].ToString() != "")
                    //{
                    //    dictionary.Add("promocodeaction", row["PromocodeAction"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("promocodeaction", "No Information");
                    //}

                    //if (row["AppLogin"].ToString() != "")
                    //{
                    //    dictionary.Add("applogin", row["AppLogin"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("applogin", "No Information");
                    //}

                    //if (row["WelcomeImage"].ToString() != "")
                    //{
                    //    dictionary.Add("welcomeimage", row["WelcomeImage"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("welcomeimage", "No Information");
                    //}

                    //if (row["ilogo_pic_chg_time"].ToString() != "")
                    //{
                    //    dictionary.Add("ilogo_pic_chg_time", row["ilogo_pic_chg_time"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("ilogo_pic_chg_time", "No Information");
                    //}

                    //if (row["DbStatus"].ToString() != "")
                    //{
                    //    dictionary.Add("dbstatus", row["DbStatus"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("dbstatus", "No Information");
                    //}

                    //if (row["SponsorImage"].ToString() != "")
                    //{
                    //    dictionary.Add("SponsorImage", row["SponsorImage"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("SponsorImage", "No Information");
                    //}

                    //if (row["Status"].ToString() != "")
                    //{
                    //    dictionary.Add("Status", row["Status"].ToString());
                    //}
                    //else
                    //{
                    //    dictionary.Add("Status", "No Information");
                    //}
                    #endregion

                    userObject = new User(Convert.ToInt32(row["user_id"].ToString()),
                                            row["user_name"].ToString(), row["password"].ToString(), row["sponsor"].ToString(),
                                            row["welcome_image"].ToString(), row["welcome_image_chg_time"].ToString(),
                                            row["iname_line"].ToString(), row["icompany_name"].ToString(), row["iemail_address"].ToString(),
                                            row["iphone_number"].ToString(), row["itagline"].ToString(), row["ilogo_pic"].ToString(), row["iwebsite"].ToString(),
                                            row["spon_change_time"].ToString(), row["AppLogin"].ToString(), row["DbStatus"].ToString(), row["Status"].ToString());

                }

            }

            //Convert dictionary to json
            return WriteJson(userObject);
        }

        public static Stream getApp_Reopen(AppReopenRequest request)
        {
            Dictionary<string, object> dictionary;
            dictionary = new Dictionary<string, object>();

            DataTable set = new DataTable();

            {
                SqlParameter[] commandParameters = new SqlParameter[7];
                commandParameters[0] = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                commandParameters[0].Value = request.UserId;// strArray_getUserID[1];
                commandParameters[1] = new SqlParameter("@paramUserName", SqlDbType.NVarChar);
                commandParameters[1].Value = request.UserName;// strArray_getUserName[1];
                commandParameters[2] = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                commandParameters[2].Value = request.Password;// strArray_getPassword[1];
                commandParameters[3] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                commandParameters[3].Value = request.promoCode;// strArray_getpromo_code[1];
                commandParameters[4] = new SqlParameter("@paramWelcomeImageChgTime", SqlDbType.NVarChar);

                if (!String.IsNullOrEmpty(request.WelcomeImageChangeTime))// strArray_getWelcomeImageChgTime[1]))
                {
                    commandParameters[4].Value = request.WelcomeImageChangeTime;// strArray_getWelcomeImageChgTime[1];
                }
                else
                {
                    commandParameters[4].Value = DBNull.Value;
                }
                commandParameters[5] = new SqlParameter("@paramSponsorChangeTime", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(request.SponChangeTime))// strArray_getSponsorCchangeTime[1]))
                {
                    commandParameters[5].Value = request.SponChangeTime;// strArray_getSponsorCchangeTime[1];
                }
                else
                {
                    commandParameters[5].Value = string.Empty;
                }
                commandParameters[6] = new SqlParameter("@paramIlogoPicChgTime", SqlDbType.NVarChar);
                if (!String.IsNullOrEmpty(request.ILogoPicChangeTime))// strArray_getIlogoPicChgTime[1]))
                {
                    commandParameters[6].Value = request.ILogoPicChangeTime;// strArray_getIlogoPicChgTime[1];
                }
                else
                {
                    commandParameters[6].Value = DBNull.Value;
                }

                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Reopen", commandParameters).Tables[0];
            }

            if (set.Rows.Count > 0)
            {
                foreach (DataRow row in set.Rows)
                {
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("userId", row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("userId", null);
                    }

                    if (row["user_name"].ToString() != "")
                    {
                        dictionary.Add("userName", row["user_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("userName", null);
                    }
                    
                    if (row["password"].ToString() != "")
                    {
                        dictionary.Add("password", row["password"].ToString());
                    }
                    else
                    {
                        dictionary.Add("password", null);
                    }

                    if (row["sponsor"] != DBNull.Value)
                    {
                        dictionary.Add("promoCode", row["sponsor"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promoCode", null);
                    }

                    if (row["sponsorDataAction"] != DBNull.Value)
                    {
                        dictionary.Add("sponsorDataAction", row["sponsorDataAction"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsorDataAction", null);
                    }

                    if (row.Table.Columns.Contains("iname_line") && row["iname_line"] != DBNull.Value)
                    {
                        dictionary.Add("iNameLine", row["iname_line"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iNameLine", null);
                    }
                    if (row.Table.Columns.Contains("iadmin_link") && row["iadmin_link"] != DBNull.Value)
                    {
                        dictionary.Add("iAdminLink", row["iadmin_link"].ToString());
                    }
                    else
                    {
                        if (row.Table.Columns.Contains("icompany_name") && row["icompany_name"] != DBNull.Value)
                        {
                            dictionary.Add("iAdminLink", row["icompany_name"].ToString());
                        }
                        else
                        {
                            dictionary.Add("iAdminLink", "Compliments Of");
                        }
                    }
                    if (row.Table.Columns.Contains("icompany_name") && row["icompany_name"] != DBNull.Value)
                    {
                        dictionary.Add("iCompanyName", row["icompany_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iCompanyName", null);
                    }
                    if (row.Table.Columns.Contains("iemail_address") && row["iemail_address"] != DBNull.Value)
                    {
                        dictionary.Add("iEmailAddress", row["iemail_address"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iEmailAddress", null);
                    }
                    if (row.Table.Columns.Contains("iphone_number") && row["iphone_number"] != DBNull.Value)
                    {
                        dictionary.Add("iPhoneNumber", row["iphone_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iPhoneNumber", null);
                    }
                    if (row.Table.Columns.Contains("itagline") && row["itagline"] != DBNull.Value)
                    {
                        dictionary.Add("iTagLine", row["itagline"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iTagLine", null);
                    }

                    if (row.Table.Columns.Contains("iwebsite") && row["iwebsite"] != DBNull.Value)
                    {
                        dictionary.Add("iWebSite", row["iwebsite"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iWebSite", null);
                    }

                    if (row["spon_change_time"] != DBNull.Value)
                    {
                        dictionary.Add("sponChangeTime", row["spon_change_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponChangeTime", null);
                    }

                    if (row["sponsorImage"] != DBNull.Value)
                    {
                        dictionary.Add("sponsorImage", row["sponsorImage"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsorImage", null);
                    }

                    if (row["ilogo_pic"] != DBNull.Value)
                    {
                        dictionary.Add("iLogoPic", row["ilogo_pic"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPic", null);
                    }

                    if (row["ilogo_pic_chg_time"] != DBNull.Value)
                    {
                        dictionary.Add("iLogoPicChangeTime", row["ilogo_pic_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("iLogoPicChangeTime", null);
                    }


                    if (row["welcome_image"] != DBNull.Value)
                    {
                        dictionary.Add("welcomeImageUrl", row["welcome_image"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImageUrl", null);
                    }

                    if (row["WelcomeImage"] != DBNull.Value)
                    {
                        dictionary.Add("welcomeImage", row["WelcomeImage"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImage", null);
                    }

                    if (row["welcome_image_chg_time"] != DBNull.Value)
                    {
                        dictionary.Add("welcomeImageChangeTime", row["welcome_image_chg_time"].ToString());
                    }
                    else
                    {
                        dictionary.Add("welcomeImageChangeTime", null);
                    }


                    if (row["PromocodeAction"] != DBNull.Value)
                    {
                        dictionary.Add("promoCodeAction", row["PromocodeAction"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promoCodeAction", null);
                    }

                    if (row["AppLogin"] != DBNull.Value)
                    {
                        dictionary.Add("appLogin", row["AppLogin"].ToString());
                    }
                    else
                    {
                        dictionary.Add("appLogin", null);
                    }

                    if (row["sponsorStatus"] != DBNull.Value)
                    {
                        dictionary.Add("sponsorStatus", row["sponsorStatus"].ToString());
                    }
                    else
                    {
                        dictionary.Add("sponsorStatus", null);
                    }
                }

            }

            //Convert dictionary to json
            return WriteJson(dictionary);
        }

        public static Stream getApp_Check_Promocode(string postdata)
        {
            DataTable set = new DataTable();
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();           
         
                {
                    SqlParameter[] commandParameters = new SqlParameter[1];
                    commandParameters[0] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                    commandParameters[0].Value = postdata;
                    set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Check_Promocode", commandParameters).Tables[0];
                }
                
            
            if (set.Rows.Count > 0)
            {
                foreach (DataRow dr in set.Rows)
                {
                    if (dr["promocode"]!=null && dr["promocode"].ToString() != "")
                    {
                        dictionary.Add("promocode", dr["promocode"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promocode", null);
                    }
                    if (dr["PromocodeCheck"]!=null && dr["PromocodeCheck"].ToString() != "")
                    {
                        dictionary.Add("PromocodeCheck", dr["PromocodeCheck"].ToString());
                    }
                    else
                    {
                        dictionary.Add("PromocodeCheck", null);
                    }
                }
            }
            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_Check_Promocode(CheckPromocodeRequest request)
        {
            DataTable set = new DataTable();
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();

            {
                SqlParameter[] commandParameters = new SqlParameter[1];
                commandParameters[0] = new SqlParameter("@paramPromoCode", SqlDbType.NVarChar);
                commandParameters[0].Value = request.PromoCode;
                set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_Check_Promocode", commandParameters).Tables[0];
            }


            if (set.Rows.Count > 0)
            {
                foreach (DataRow dr in set.Rows)
                {
                    if (dr.Table.Columns.Contains("promocode") && dr["promocode"] != null && dr["promocode"].ToString() != "")
                    {
                        dictionary.Add("promocode", dr["promocode"].ToString());
                    }
                    else
                    {
                        dictionary.Add("promocode", null);
                    }
                    if (dr.Table.Columns.Contains("PromocodeCheck") && dr["PromocodeCheck"] != null && dr["PromocodeCheck"].ToString() != "")
                    {
                        dictionary.Add("PromocodeCheck", dr["PromocodeCheck"].ToString());
                    }
                    else
                    {
                        dictionary.Add("PromocodeCheck", null);
                    }
                }
            }
            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_PES_GetCountryStates()
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable  set = new DataTable();
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetCountryStates").Tables[0];
            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    if (row["country"] != null && row["country"].ToString() != "")
                    {
                        dictionary.Add("country" + count, row["country"].ToString());
                    }
                    else
                    {
                        dictionary.Add("country", null);
                    }
                    if (row["state_or_province_name"] != null && row["state_or_province_name"].ToString() != "")
                    {
                        dictionary.Add("stateOrProvinceName" + count, row["state_or_province_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("stateOrProvinceName", null);
                    }
                    if (row["state_province_code"] != null && row["state_province_code"].ToString() != "")
                    {
                        dictionary.Add("stateProvinceCode" + count, row["state_province_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("stateProvinceCode", null);
                    }
                    if (row["country_state_code"] != null && row["country_state_code"].ToString() != "")
                    {
                        dictionary.Add("countryStateCode" + count, row["country_state_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("countryStateCode", null);
                    }
                    if (row["country_name"] != null && row["country_name"].ToString() != "")
                    {
                        dictionary.Add("countryName" + count, row["country_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("countryName", null);
                    }
                    count++;
                }
            }
            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_PES_GetDwellingTypes()
        {

            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetDwellingTypes").Tables[0];
            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    
                    if (row["dwelling_code"] != null && row["dwelling_code"].ToString() != "")
                    {
                        dictionary.Add("dwelling_code" + count, row["dwelling_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_code" + count, null);
                    }
                    if (row["dwelling_type"] != null && row["dwelling_type"].ToString() != "")
                    {
                        dictionary.Add("dwelling_type" + count, row["dwelling_type"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_type" + count, null);
                    }
                   
                    count++;
                }
            }
            return WriteJson(new { dictionary = dictionary });            
        }

        public static Stream getApp_PES_GetFeaturesByDwellingCode(Stream stream)
        {
            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strArray_getDwellingCode = str_getSplitPassParameters[0].Split(new char[] { '=' });

            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@dwellingCode", SqlDbType.Int);
            commandParameters[0].Value = strArray_getDwellingCode[1];
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetFeaturesByDwellingCode", commandParameters).Tables[0];
            
            if (set.Rows.Count > 0)
            {
                int count=1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["dwelling_code"].ToString() != "")
                    {
                        dictionary.Add("dwelling_code" + count, row["dwelling_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_code" + count, null);
                    }
                    if (row["dwelling_type"].ToString() != "")
                    {
                        dictionary.Add("dwelling_type" + count, row["dwelling_type"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_type" + count, null);
                    }
                    if (row["categoryID"].ToString() != "")
                    {
                        dictionary.Add("categoryID" + count, row["categoryID"].ToString());
                    }
                    else
                    {
                        dictionary.Add("categoryID" + count, null);
                    }
                    if (row["HomeCareLibrary_Category"].ToString() != "")
                    {
                        dictionary.Add("HomeCareLibrary_Category" + count, row["HomeCareLibrary_Category"].ToString());
                    }
                    else
                    {
                        dictionary.Add("HomeCareLibrary_Category" + count, null);
                    }
                    if (row["feature_code"].ToString() != "")
                    {
                        dictionary.Add("feature_code" + count, row["feature_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_code" + count, null);
                    }
                    if (row["feature_name"].ToString() != "")
                    {
                        dictionary.Add("feature_name" + count, row["feature_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_name" + count, null);
                    }
                    if (row["feature_url"].ToString() != "")
                    {
                        dictionary.Add("feature_url" + count, row["feature_url"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_url" + count, null);
                    }
                    if (row["feature_description"].ToString() != "")
                    {
                        dictionary.Add("feature_description" + count, row["feature_description"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_description" + count, null);
                    }
                }
                
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary});
        }

        public static Stream getApp_PES_GetFeaturesByDwellingCode(GetFeaturesByDwellingTypeRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();

            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@dwellingCode", SqlDbType.Int);
            commandParameters[0].Value = request.DwellingCode;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetFeaturesByDwellingCode", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["dwelling_code"].ToString() != "")
                    {
                        dictionary.Add("dwelling_code" + count, row["dwelling_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_code" + count, null);
                    }
                    if (row["dwelling_type"].ToString() != "")
                    {
                        dictionary.Add("dwelling_type" + count, row["dwelling_type"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_type" + count, null);
                    }
                    if (row["categoryID"].ToString() != "")
                    {
                        dictionary.Add("categoryID" + count, row["categoryID"].ToString());
                    }
                    else
                    {
                        dictionary.Add("categoryID" + count, null);
                    }
                    if (row["HomeCareLibrary_Category"].ToString() != "")
                    {
                        dictionary.Add("HomeCareLibrary_Category" + count, row["HomeCareLibrary_Category"].ToString());
                    }
                    else
                    {
                        dictionary.Add("HomeCareLibrary_Category" + count, null);
                    }
                    if (row["feature_code"].ToString() != "")
                    {
                        dictionary.Add("feature_code" + count, row["feature_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_code" + count, null);
                    }
                    if (row["feature_name"].ToString() != "")
                    {
                        dictionary.Add("feature_name" + count, row["feature_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_name" + count, null);
                    }
                    if (row["feature_url"].ToString() != "")
                    {
                        dictionary.Add("feature_url" + count, row["feature_url"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_url" + count, null);
                    }
                    if (row["feature_description"].ToString() != "")
                    {
                        dictionary.Add("feature_description" + count, row["feature_description"].ToString());
                    }
                    else
                    {
                        dictionary.Add("feature_description" + count, null);
                    }
                }

            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
        }

        public static Stream getApp_PES_GetKnowledgeLevelByUserIdHomeNumber(Stream stream)
        {
            StreamReader reader = new StreamReader(stream);
            string[] str_getSplitPassParameters = reader.ReadToEnd().Split(new char[] { '/' });

            string[] strArray_getUserId = str_getSplitPassParameters[0].Split(new char[] { '=' });
            string[] strArray_getHomeNumber = str_getSplitPassParameters[1].Split(new char[] { '=' });

            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = strArray_getUserId[1];
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = strArray_getHomeNumber[1];
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetKnowledgeLevelByUserIdHomeNumber", commandParameters).Tables[0];
                        
            if (set.Rows.Count > 0)
            {
                int count=1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["ps_knowledge_level"].ToString() != "")
                    {
                        dictionary.Add("ps_knowledge_level" + count, row["ps_knowledge_level"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_knowledge_level" + count, "No Information");
                    }                    
                }                
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary});
             
        }

        public static Stream getApp_PES_GetKnowledgeLevelByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetKnowledgeLevelByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["ps_knowledge_level"].ToString() != "")
                    {
                        dictionary.Add("ps_knowledge_level" + count, row["ps_knowledge_level"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_knowledge_level" + count, "No Information");
                    }
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });

        }

        public static Stream getApp_PES_GetLocationByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetLocationByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["ps_country"].ToString() != "")
                    {
                        dictionary.Add("ps_country" + count, row["ps_country"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_country" + count, "No Information");
                    }
                    if (row["ps_state_province"].ToString() != "")
                    {
                        dictionary.Add("ps_state_province" + count, row["ps_state_province"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_state_province" + count, "No Information");
                    }
                    if (row["country"].ToString() != "")
                    {
                        dictionary.Add("country" + count, row["country"].ToString());
                    }
                    else
                    {
                        dictionary.Add("country" + count, "No Information");
                    }
                    if (row["state_or_province_name"].ToString() != "")
                    {
                        dictionary.Add("state_or_province_name" + count, row["state_or_province_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("state_or_province_name" + count, "No Information");
                    }
                    if (row["country_state_code"].ToString() != "")
                    {
                        dictionary.Add("country_state_code" + count, row["country_state_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("country_state_code" + count, "No Information");
                    }
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
             
        }

        public static Stream getApp_PES_GetPriorityLevelByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetPriorityLevelByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["ps_priority"].ToString() != "")
                    {
                        dictionary.Add("ps_priority" + count, row["ps_priority"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_priority" + count, "No Information");
                    }                 
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
             
        }

        public static Stream getApp_PES_GetMonthlyAlertSettingsByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetMonthlyAlertSettingsByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["web_mo_alert_email"].ToString() != "")
                    {
                        dictionary.Add("web_mo_alert_email" + count, row["web_mo_alert_email"].ToString());
                    }
                    else
                    {
                        dictionary.Add("web_mo_alert_email" + count, "No Information");
                    }
                    if (row["app_mo_alert_week"].ToString() != "")
                    {
                        dictionary.Add("app_mo_alert_week" + count, row["app_mo_alert_week"].ToString());
                    }
                    else
                    {
                        dictionary.Add("app_mo_alert_week" + count, "No Information");
                    }
                    if (row["app_mo_alert_day"].ToString() != "")
                    {
                        dictionary.Add("app_mo_alert_day" + count, row["app_mo_alert_day"].ToString());
                    }
                    else
                    {
                        dictionary.Add("app_mo_alert_day" + count, "No Information");
                    }
                    if (row["app_mo_alert_hour"].ToString() != "")
                    {
                        dictionary.Add("app_mo_alert_hour" + count, row["app_mo_alert_hour"].ToString());
                    }
                    else
                    {
                        dictionary.Add("app_mo_alert_hour" + count, "No Information");
                    }
                    if (row["app_mo_alert_badge"].ToString() != "")
                    {
                        dictionary.Add("app_mo_alert_badge" + count, row["app_mo_alert_badge"].ToString());
                    }
                    else
                    {
                        dictionary.Add("app_mo_alert_badge" + count, "No Information");
                    }
                    if (row["app_mo_alert_popup"].ToString() != "")
                    {
                        dictionary.Add("app_mo_alert_popup" + count, row["app_mo_alert_popup"].ToString());
                    }
                    else
                    {
                        dictionary.Add("app_mo_alert_popup" + count, "No Information");
                    }
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
            
        }

        public static Stream getApp_PES_GetGoalsByUserIdHomeNumber(GetKLByUserIdHomeNumberRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.Int);
            commandParameters[0].Value = request.UserId;
            commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.Int);
            commandParameters[1].Value = request.HomeNumber;
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetGoalsByUserIdHomeNumber", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["ps_goal_string"].ToString() != "")
                    {
                        dictionary.Add("ps_goal_string" + count, row["ps_goal_string"].ToString());
                    }
                    else
                    {
                        dictionary.Add("ps_goal_string" + count, "No Information");
                    }
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
             
        }

        public static Stream getApp_PES_GetUserHomes(GetUserHomeRequest request)
        {
            Dictionary<string, string> dictionary;
            dictionary = new Dictionary<string, string>();
            DataTable set = new DataTable();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@userId", SqlDbType.NVarChar);
            commandParameters[0].Value = request.UserId.ToString();
            set = SqlHelper.ExecuteDataset(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_GetUserHomes", commandParameters).Tables[0];

            if (set.Rows.Count > 0)
            {
                int count = 1;
                foreach (DataRow row in set.Rows)
                {
                    count++;
                    if (row["user_id"].ToString() != "")
                    {
                        dictionary.Add("user_id" + count, row["user_id"].ToString());
                    }
                    else
                    {
                        dictionary.Add("user_id" + count, "No Information");
                    }
                    if (row["home_number"].ToString() != "")
                    {
                        dictionary.Add("home_number" + count, row["home_number"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_number" + count, "No Information");
                    }
                    if (row["home_name"].ToString() != "")
                    {
                        dictionary.Add("home_name" + count, row["home_name"].ToString());
                    }
                    else
                    {
                        dictionary.Add("home_name" + count, "No Information");
                    }
                    if (row["dwelling_type"].ToString() != "")
                    {
                        dictionary.Add("dwelling_type" + count, row["dwelling_type"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_type" + count, "No Information");
                    }
                    if (row["dwelling_code"].ToString() != "")
                    {
                        dictionary.Add("dwelling_code" + count, row["dwelling_code"].ToString());
                    }
                    else
                    {
                        dictionary.Add("dwelling_code" + count, "No Information");
                    }
                }
            }

            //Convert dictionary to json
            return WriteJson(new { dictionary = dictionary });
             
        }

        //public static Stream getApp_PES_UpdatePersonalization(PersonalizationData personlisationData)
        //{
        //    SqlParameter[] commandParameters = new SqlParameter[12];
        //    commandParameters[0] = new SqlParameter("@userID", SqlDbType.NVarChar);
        //    commandParameters[0].Value = personlisationData.UserId;
        //    commandParameters[1] = new SqlParameter("@homeNumber", SqlDbType.NVarChar);
        //    commandParameters[1].Value = personlisationData.HomeNumber;
        //    commandParameters[2] = new SqlParameter("@homeName", SqlDbType.NVarChar);
        //    commandParameters[2].Value = personlisationData.HomeName;
        //    commandParameters[3] = new SqlParameter("@dwellingType", SqlDbType.NVarChar);
        //    commandParameters[3].Value = personlisationData.DwellingType;
        //    commandParameters[4] = new SqlParameter("@featurestring", SqlDbType.NVarChar);
        //    commandParameters[4].Value = personlisationData.FeatureString;
        //    commandParameters[5] = new SqlParameter("@psPriority", SqlDbType.NVarChar);
        //    commandParameters[5].Value = personlisationData.Priority;
        //    commandParameters[6] = new SqlParameter("@psKnowledgeLevel", SqlDbType.NVarChar);
        //    commandParameters[6].Value = personlisationData.KnowledgeLevel;
        //    commandParameters[7] = new SqlParameter("@psCountry", SqlDbType.NVarChar);
        //    commandParameters[7].Value = personlisationData.Country;
        //    commandParameters[8] = new SqlParameter("@psStateProvince", SqlDbType.NVarChar);
        //    commandParameters[8].Value = personlisationData.StateProvince;
        //    commandParameters[9] = new SqlParameter("@psGoalString", SqlDbType.NVarChar);
        //    commandParameters[9].Value = personlisationData.Goal;
        //    commandParameters[10] = new SqlParameter("@welcomeImage", SqlDbType.NVarChar);
        //    commandParameters[10].Value = personlisationData.WelcomeImage;
        //    commandParameters[11] = new SqlParameter("@webMoAlert", SqlDbType.NVarChar);
        //    commandParameters[11].Value = personlisationData.WebMOAlertEmail;

        //    var result = SqlHelper.ExecuteScalar(Convert.ToString(ConfigurationManager.ConnectionStrings["ConnectionString"]), CommandType.StoredProcedure, "sp_App_PES_UpdatePersonalization", commandParameters);
            
        //    //Convert dictionary to json
        //    return WriteJson(new { result=result });
        //}
              
        private static Stream WriteJson(object value)
        {
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(serializer.Serialize(value)));
            return stream;
        }
    }
}